package org.example.dao;

import org.example.entity.Salary;
import org.example.utils.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;

public class SalaryDAO {
    public static boolean insertSalary(Salary salary) {
        try (Connection connection = JdbcUtils.getConnection()) {
            String sql = "INSERT INTO ims_salary (sa_id,emp_id,sa_date,sa_base,sa_performance,sa_insurance,sa_actual,create_time,update_time) VALUES (?,?,?,?,?,?,?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, salary.getSaId());
            preparedStatement.setString(2, salary.getEmpId());
            preparedStatement.setString(3, salary.getSaDate());
            preparedStatement.setDouble(4, salary.getSaBase());
            preparedStatement.setDouble(5, salary.getSaPerformance());
            preparedStatement.setDouble(6, salary.getSaInsurance());
            preparedStatement.setDouble(7, salary.getSaActual());
            preparedStatement.setObject(8, new java.sql.Timestamp(new java.util.Date().getTime()));
            preparedStatement.setObject(9, new java.sql.Timestamp(new java.util.Date().getTime()));

            int i = preparedStatement.executeUpdate();
            return i == 1;
        } catch (SQLException e) {
            System.out.println("用户输入数据过大，请重新输入");
            return false;
        }
    }

    public static boolean checkCurrentMonthSalary(String currentDate,String empId) {
        try (Connection connection = JdbcUtils.getConnection()) {
            String sql = "SELECT sa_id FROM ims_salary WHERE is_deleted = 0 AND sa_date = ? AND emp_id = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, currentDate);
            preparedStatement.setString(2, empId);
            ResultSet res = preparedStatement.executeQuery();
            return res.next();
        } catch (SQLException e) {
            System.out.println("数据库操作失败！请联系技术解决："+e.getMessage());
            return false;
        }
    }

    public static ArrayList<Double> querySalaryByEmpId(String empId, String currentDate) {
        try (Connection connection = JdbcUtils.getConnection()) {
            String sql = "SELECT sa_base,sa_performance,sa_insurance FROM ims_salary WHERE is_deleted = 0 AND emp_id = ? AND sa_date = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, empId);
            preparedStatement.setString(2, currentDate);
            ResultSet res = preparedStatement.executeQuery();

            ArrayList<Double> salaryList = new ArrayList<>();
            if (res.next()){
                salaryList.add(res.getDouble("sa_base"));
                salaryList.add(res.getDouble("sa_performance"));
                salaryList.add(res.getDouble("sa_insurance"));
            }
            return salaryList;
        } catch (Exception e) {
            System.out.println("数据库操作失败！请联系技术解决："+e.getMessage());
            return null;
        }
    }

    public static boolean updateSalary(Double newSaBase, Double newSaPerformance, Double newSaInsurance, String empId, String currentDate, double newSaActual) {
        try (Connection connection = JdbcUtils.getConnection()) {
            String sql = "UPDATE ims_salary SET sa_base = ?,sa_performance = ?,sa_insurance = ?,sa_actual = ?,update_time = ? WHERE emp_id = ? AND sa_date = ? AND is_deleted = 0";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setDouble(1, newSaBase);
            preparedStatement.setDouble(2, newSaPerformance);
            preparedStatement.setDouble(3, newSaInsurance);
            preparedStatement.setDouble(4, newSaActual);
            preparedStatement.setObject(5, new java.sql.Timestamp(new java.util.Date().getTime()));
            preparedStatement.setString(6, empId);
            preparedStatement.setString(7, currentDate);

            int i = preparedStatement.executeUpdate();
            return i == 1;
        } catch (SQLException e) {
            System.out.println("数据库操作失败！请联系技术解决："+e.getMessage());
            return false;
        }
    }

    public static boolean deleteSalary(String empId, String currentMonth) {
        try (Connection connection = JdbcUtils.getConnection()) {
            String sql = "UPDATE ims_salary SET is_deleted = 1 WHERE  emp_id = ? AND sa_date = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, empId);
            preparedStatement.setString(2, currentMonth);
            int i = preparedStatement.executeUpdate();
            return i == 1;
        } catch (SQLException e) {
            System.out.println("数据库操作失败！请联系技术解决："+e.getMessage());
            return false;
        }
    }

    public static ArrayList<String[]> querySalaryListByEmpId(String empId) {
        try (Connection connection = JdbcUtils.getConnection()) {
            String sql = "SELECT ims_salary.sa_id,ims_salary.sa_insurance,ims_salary.sa_actual,ims_employee.emp_name,ims_salary.sa_date,ims_salary.sa_performance,ims_salary.sa_base FROM ims_salary " +
                    "LEFT JOIN ims_employee ON ims_salary.emp_id = ims_employee.emp_id WHERE ims_salary.is_deleted = 0 AND ims_salary.emp_id = ? ";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, empId);
            ResultSet res = preparedStatement.executeQuery();

            ArrayList<String[]> salaryList = new ArrayList<>();
            if (res.next()){
                String[] resColumn = new String[7];
                resColumn[0] = res.getString("sa_id");
                resColumn[1] = res.getString("sa_insurance");
                resColumn[2] = res.getString("sa_actual");
                resColumn[3] = res.getString("emp_name");
                resColumn[4] = res.getString("sa_date");
                resColumn[5] = res.getString("sa_performance");
                resColumn[6] = res.getString("sa_base");
                salaryList.add(resColumn);
            }
            return salaryList;
        } catch (Exception e) {
            System.out.println("数据库操作失败！请联系技术解决："+e.getMessage());
            return null;
        }
    }

    public static ArrayList<String[]> querySalaryListBySaDate(String currentMonth) {
        try (Connection connection = JdbcUtils.getConnection()) {
            String sql = "SELECT ims_salary.sa_id,ims_salary.sa_insurance,ims_salary.sa_actual,ims_employee.emp_name,ims_salary.sa_date,ims_salary.sa_performance,ims_salary.sa_base FROM ims_salary " +
                    "LEFT JOIN ims_employee ON ims_salary.emp_id = ims_employee.emp_id WHERE ims_salary.is_deleted = 0 AND ims_employee.is_deleted = 0 AND ims_salary.sa_date = ? ";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, currentMonth);
            ResultSet res = preparedStatement.executeQuery();

            ArrayList<String[]> salaryList = new ArrayList<>();
            while (res.next()){
                String[] resColumn = new String[7];
                resColumn[0] = res.getString("sa_id");
                resColumn[1] = res.getString("sa_insurance");
                if ( Double.parseDouble(res.getString("sa_actual")) <= 0){
                    resColumn[2] = "0";
                }else {
                    resColumn[2] = res.getString("sa_actual");
                }
                resColumn[3] = res.getString("emp_name");
                resColumn[4] = res.getString("sa_date");
                resColumn[5] = res.getString("sa_performance");
                resColumn[6] = res.getString("sa_base");
                salaryList.add(resColumn);
            }
            return salaryList;
        } catch (Exception e) {
            System.out.println("数据库操作失败！请联系技术解决："+e.getMessage());
            return null;
        }
    }

    public static boolean deleteSalaryById(String id) {
        try (Connection connection = JdbcUtils.getConnection()) {
            String sql = "UPDATE ims_salary SET is_deleted = 1 WHERE  emp_id = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, id);
            int i = preparedStatement.executeUpdate();
            return i >= 1;
        } catch (SQLException e) {
            System.out.println("数据库操作失败！请联系技术解决："+e.getMessage());
            return false;
        }
    }
}
